import pymysql


class DBTools(object):

    @staticmethod
    def __create_conn():
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn

    """查询一条"""

    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_conn()  # 使用类名调用静态方法，创建连接
            cursor = conn.cursor()  # 创建游标
            cursor.execute(sql)  # 执行用户传入的查询sql语句
            res = cursor.fetchone()  # 提取1条

        except Exception as err:
            print("代码异常", err)
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭连接
            return res  # 返回查询结果

    @staticmethod
    def query_all(sql):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.rownumber =0
            res = cursor.fetchall()
        except Exception as err:
            print("代码异常", err)
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭连接
            return res  # 返回查询结果
    @staticmethod
    def query_many(size, sql):
        conn = None
        cursor = None
        res = None
        try:
            conn =DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(size)
        except Exception as err:
            print("代码异常", err)
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭连接
            return res  # 返回查询结果

    """增、删、改"""

    @staticmethod
    def db_iud(sql):
        conn = None
        cursor = None

        try:
            conn = DBTools.__create_conn()  # 使用类名调用静态方法，创建连接
            cursor = conn.cursor()  # 创建游标
            cursor.execute(sql)  # 执行用户传入的增删改sql语句
            conn.commit()  # 提交事务
            print("执行成功")

        except Exception as err:
            print("代码异常",str(err))
            conn.rollback()
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭连接


if __name__ == '__main__':
    res = DBTools.query_one("select * from t_book where id = 102")
    print("查询结果", res)

